﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMTC.Model.Domain;
using System.Data.Common;
using IMPlant.Core.Data.Unity;

namespace SMTC.Dao.DataServer
{
    public class DataServerDao : BaseDao
    {
        /// <summary>
        /// 获取链路服务器列表
        /// </summary>
        private const string SQL_GET_SERVER_LIST = @"SELECT srv.name,srv.product,srv.provider,srv.data_source,srv.modify_date 
                                                      FROM sys.servers srv 
                                                      WHERE srv.server_id != 0 AND srv.name LIKE @name";
        /// <summary>
        /// 判断是否存在视图
        /// </summary>
        private const string SQL_ISHAVE_DATEVIEW = @"SELECT COUNT(1) FROM sys.VIEWS WHERE [name] = 'view_DataService'";
        /// <summary>
        /// 抽取元件数量
        /// </summary>
        private const string SQL_UPDATE_COUNT = @"UPDATE dbo.SMT_HalfToProgram SET UnitCount = v.lindex,OperationTime = GETDATE(),Operation = 'Sys'
                                                FROM  dbo.view_DataService v  LEFT JOIN dbo.SMT_HalfToProgram b
                                                ON b.ProgramName = v.objectName";
        /// <summary>
        /// 抽取原件数量
        /// </summary>
        /// <returns></returns>
        public int GetUnitCount()
        {
            return DBTemplete.ExcuteNoQuery(System.Data.CommandType.Text, SQL_UPDATE_COUNT);
        }


        /// <summary>
        /// 判断是否存在视图
        /// </summary>
        /// <returns></returns>
        public bool ISHaveDateView()
        {
            int result = DBTemplete.ExcuteGetObject<int>(System.Data.CommandType.Text, SQL_ISHAVE_DATEVIEW);
            if (result == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool ModifyDataView(string DataServerName)
        {
            List<DataServerDomain> list = this.GetServerList("%%");
            StringBuilder strCommand = new StringBuilder();
            StringBuilder strCPlacementLink = new StringBuilder();
            StringBuilder strAliasName = new StringBuilder();

            if (this.ISHaveDateView() == true)
            {
                strCommand.Append(@"ALTER view [dbo].[view_DataService]
                              as ");
            }
            else
            {
                strCommand.Append(@"CREATE view [dbo].[view_DataService]
                              as ");
            }

            foreach (var item in list)
            {
                if (strCPlacementLink.Length > 0)
                {
                    strCPlacementLink.Append(" UNION  SELECT * FROM [" + item.name + "]." + DataServerName + ".dbo.CComponentPlacement");
                }
                else
                {
                    strCPlacementLink.Append(" SELECT * FROM [" + item.name + "]." + DataServerName + ".dbo.CComponentPlacement");
                }

                if (strAliasName.Length > 0)
                {
                    strAliasName.Append(" UNION  SELECT * FROM [" + item.name + "]." + DataServerName + ".dbo.AliasName");
                }
                else
                {
                    strAliasName.Append(" SELECT * FROM [" + item.name + "]." + DataServerName + ".dbo.AliasName");
                }                
            }

            strCommand.Append(" WITH CPlacementLink AS ( " + strCPlacementLink.ToString()
                                    + "),AliasName AS ( " + strAliasName.ToString() + ")");
            strCommand.Append(@", pids as
                                 (
                                    select pid,count(pid) lIndex from CPlacementLink
                                    group by PID 
                                ) 
                                select DISTINCT p.pid,an.ObjectName,lindex 
                                from pids p left join AliasName an
                                on p.pid = an.pid");
            if (list == null || list.Count == 0)
            {
                strCommand.Clear();
                strCommand.Append(@"IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[view_DataService]'))
                                    DROP VIEW [dbo].[view_DataService]");
            }

            try
            {
                DBTemplete.ExcuteNoQuery(System.Data.CommandType.Text, strCommand.ToString());
                return true;
            }
            catch
            {
                return false;
            }

        }

        /// <summary>
        /// 获取链路服务器列表
        /// </summary>
        /// <param name="Name">服务名</param>
        /// <returns></returns>
        public List<DataServerDomain> GetServerList(string Name)
        {
            DbParameter[] parameter = 
            {
               DBparameterBuild.Build("@name",Name)               
            };

            return DBTemplete.ExcuteGetList<DataServerDomain>(System.Data.CommandType.Text, SQL_GET_SERVER_LIST, parameter);
        }

        public bool DelServer(string Name)
        {
            string SQL = @"IF  EXISTS (
              SELECT * 
              FROM sys.servers srv 
              WHERE srv.server_id != 0 AND srv.name = N'" + Name + @"'
             )
             EXEC master.dbo.sp_dropserver @server=N'" + Name + @"', @droplogins='droplogins'";

            try
            {
                DBTemplete.ExcuteNoQuery(System.Data.CommandType.Text, SQL);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool AddServer(string IP, string userName, string PassWord)
        {
            string SQL = @"EXEC master.dbo.sp_addlinkedserver @server = N'" + IP + @"', @srvproduct=N'SQL Server'
                           EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'" + IP + @"',@useself=N'False',@locallogin=NULL,@rmtuser=N'" + userName + "',@rmtpassword='" + PassWord + "'";
            try
            {
                DBTemplete.ExcuteNoQuery(System.Data.CommandType.Text, SQL);
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}
